﻿--2012 08 01 新增字段

-- =============================================
-- Author:		<Author,@XFL>
-- Create date: <Create Date,20110113>
-- Description:	<Description,用于工资汇总>
-- =============================================
CREATE PROCEDURE [dbo].[proc_Wage_Getlist_Gather]
	(
		@Month_Start nvarchar(7),
		@Month_End nvarchar(7),		
		@CompanyId int,
		@DepId int,
		@GradeId int,
		@SerchType int 
	)
AS
BEGIN


	if(@SerchType=1)
		Begin
		-- 执行 查询全部单位的汇总，注意此时传入的 公司ID 为 0

		declare @list_table_1 table(
		id int identity(1,1)
		,CompanyId int
		,BasicWage Decimal(18,2)
		,PostWage Decimal(18,2)
		,WorkWage Decimal(18,2)
		,JxWage Decimal(18,2)
		,ManQin Decimal(18,2)
		,Allowance DECIMAL(18,2)
		,FixedAllowance Decimal(18,2)
		,QtAllowance Decimal(18,2)
		,TiCheng Decimal(18,2)
		,MTiCheng Decimal(18,2)
		,YjBonus Decimal(18,2)
		,LsBonus Decimal(18,2)
		,ChuQinKouKuan Decimal(18,2)
		,BxKouKuan Decimal(18,2)
		,ChiDaoKouKuan Decimal(18,2)
		,YJKouKuan Decimal(18,2)
		,OtherKouKuan Decimal(18,2)
		,Count_Sum INT
		--
		,XSTC Decimal(18,2)
		,XFTC Decimal(18,2)
		,BLTC Decimal(18,2)
		,LSwance Decimal(18,2)
		,ZBwance Decimal(18,2)
		,GRSDSKouKuan Decimal(18,2)
		,GJJKouKuan Decimal(18,2)
		,YFHeJi Decimal(18,2)
		--
		)

		;WITH list As(
				Select 
				CompanyId
				,BasicWage
				,PostWage
				,WorkWage
				,JxWage
				,ManQin
				,Allowance
				,FixedAllowance
				,QtAllowance
				,TiCheng
				,MTiCheng
				,YjBonus
				,LsBonus
				,ChuQinKouKuan
				,BxKouKuan
				,ChiDaoKouKuan
				,YJKouKuan
				,OtherKouKuan,isnull(Deduction,0) as Deduction
				,FaFangMonth
				,1 as Count_Sum
				--
				,XSTC
				,XFTC
				,BLTC
				,LSwance
				,ZBwance
				,GRSDSKouKuan
				,GJJKouKuan
				,YFHeJi
				--
				From Employee_Wage  
				Where (@GradeId=0 Or GradeId=@GradeId  ) and FinanceFlag=2
		 and(  FaFangMonth between @Month_Start and  @Month_End)

		) 
		insert into @list_table_1(
		CompanyId	
		,BasicWage
		,PostWage
		,WorkWage
		,JxWage
		,ManQin
		,Allowance
		,FixedAllowance
		,QtAllowance
		,TiCheng
		,MTiCheng
		,YjBonus
		,LsBonus
		,ChuQinKouKuan
		,BxKouKuan
		,ChiDaoKouKuan
		,YJKouKuan
		,OtherKouKuan
		,Count_Sum
		--
		,XSTC
		,XFTC
		,BLTC
		,LSwance
		,ZBwance
		,GRSDSKouKuan
		,GJJKouKuan
		,YFHeJi
		--
		) 
		select 
		CompanyId
		,BasicWage
		,PostWage
		,WorkWage
		,JxWage
		,ManQin
		,Allowance
		,FixedAllowance
		,QtAllowance
		,TiCheng
		,MTiCheng
		,YjBonus
		,LsBonus
		,ChuQinKouKuan
		,BxKouKuan
		,ChiDaoKouKuan
		,YJKouKuan
		,OtherKouKuan+ Deduction
		,Count_Sum
		--
		,XSTC
		,XFTC
		,BLTC
		,LSwance
		,ZBwance
		,GRSDSKouKuan
		,GJJKouKuan
		,YFHeJi
		--
		From list  ;
		-- 上面完成 list 表
			--Select * from @list_table_1 
		;WITH list_Union As 
		(
		Select 
				CompanyId
				,sum (BasicWage)  as BasicWage_s
				,sum (PostWage)  as PostWage_s
				,sum (WorkWage)  as WorkWage_s
				,sum (JxWage)  as JxWage_s
				,sum (ManQin)  as ManQin_s
				,sum (Allowance)  as Allowance_s
				,sum (FixedAllowance)  as FAllowance_s
				,sum (QtAllowance)  as QtAllowance_s
				,sum (TiCheng)  as TiCheng_s
				,sum (MTiCheng)  as MTiCheng_s
				,sum (YjBonus)  as YjBonus_s
				,sum (LsBonus)  as LsBonus_s
				,sum (ChuQinKouKuan)  as ChuQinKouKuan_s
				,sum (BxKouKuan)  as BxKouKuan_s
				,sum (ChiDaoKouKuan)  as ChiDaoKouKuan_s
				,sum (YJKouKuan)  as YJKouKuan_s
				,sum (OtherKouKuan)  as OtherKouKuan_s
				,sum (Count_Sum) as Count_Sum_s
				--
				,SUM(XSTC) AS XSTC_s
				,SUM(XFTC) AS XFTC_s
				,SUM(BLTC) AS BLTC_s
				,SUM(LSwance) AS LSwance_s
				,SUM(ZBwance) AS ZBwance_s
				,SUM(GRSDSKouKuan) AS GRSDSKouKuan_s
				,SUM(GJJKouKuan) AS GJJKouKuan_s
				,sum(YFHeJi) as YFHeJi_s
				--
				From @list_table_1
				group by CompanyId

		UNION All

		Select 	
		Id as CompanyId 
				,0  as BasicWage_s
				,0  as PostWage_s
				,0  as WorkWage_s
				,0  as JxWage_s
				,0  as ManQin_s
				,0  as Allowance_s
				,0  as FAllowance_s
				,0  as QtAllowance_s
				,0  as TiCheng_s
				,0  as MTiCheng_s
				,0  as YjBonus_s
				,0  as LsBonus_s
				,0  as ChuQinKouKuan_s
				,0  as BxKouKuan_s
				,0  as ChiDaoKouKuan_s
				,0  as YJKouKuan_s
				,0  as OtherKouKuan_s
				,0  as Count_Sum_s
				--
				,0  AS XSTC_s
				,0  AS XFTC_s
				,0  AS BLTC_s
				,0  AS LSwance_s
				,0  AS ZBwance_s
				,0  AS GRSDSKouKuan_s
				,0  AS GJJKouKuan_s
				,0 as YFHeJi_s
				--
		From Company 
		where id not in 
		(
		Select CompanyId From Employee_Wage	where   FinanceFlag=2 and (@GradeId=0 or GradeId=@GradeId) and(  FaFangMonth between @Month_Start and   @Month_End) 
		)

		)
		select *
		,(Select CompanyName From Company Where Id=list_Union.CompanyId) As CompanyName
		,'00' AS bmbh --补充字段
		,'00' AS [DName]  --补充字段
		 from list_Union order by CompanyId
		END
		--执行 查询全部单位的汇总 结束



		if(@SerchType=2)
		Begin
		-- 执行 查询指定单位的全部部门汇总，注意此时传入的 部门ID 为 0

		declare @list_table_2 table(
		id int identity(1,1)
		,bmbh varchar(50)
		,BasicWage Decimal(18,2)
		,PostWage Decimal(18,2)
		,WorkWage Decimal(18,2)
		,JxWage Decimal(18,2)
		,ManQin Decimal(18,2)
		,Allowance Decimal(18,2)
		,FixedAllowance Decimal(18,2)
		,QtAllowance Decimal(18,2)
		,TiCheng Decimal(18,2)
		,MTiCheng Decimal(18,2)
		,YjBonus Decimal(18,2)
		,LsBonus Decimal(18,2)
		,ChuQinKouKuan Decimal(18,2)
		,BxKouKuan Decimal(18,2)
		,ChiDaoKouKuan Decimal(18,2)
		,YJKouKuan Decimal(18,2)
		,OtherKouKuan Decimal(18,2)
		,Count_Sum INT
		--
		,XSTC Decimal(18,2)
		,XFTC Decimal(18,2)
		,BLTC Decimal(18,2)
		,LSwance Decimal(18,2)
		,ZBwance Decimal(18,2)
		,GRSDSKouKuan Decimal(18,2)
		,GJJKouKuan Decimal(18,2)
		,YFHeJi Decimal(18,2)
		--
		)
		;WITH list As(
				Select 
				DepartmentId
				,(SELECT Convert(nchar(4), bmbh)  FROM department WHERE id=Employee_Wage.DepartmentId)  AS bmbh
				,DepName
				,GradeId
				,BasicWage
				,PostWage
				,WorkWage
				,JxWage
				,ManQin
				,Allowance
				,FixedAllowance
				,QtAllowance
				,TiCheng
				,MTiCheng
				,YjBonus
				,LsBonus
				,ChuQinKouKuan
				,BxKouKuan
				,ChiDaoKouKuan
				,YJKouKuan
				,OtherKouKuan,isnull(Deduction,0) as Deduction
				,FaFangMonth
				,1 as Count_Sum
				--
				,XSTC
				,XFTC
				,BLTC
				,LSwance
				,ZBwance
				,GRSDSKouKuan
				,GJJKouKuan
				,YFHeJi
				--
				From Employee_Wage  
				Where (CompanyId=@CompanyId) and (@GradeId=0 Or GradeId=@GradeId  ) and FinanceFlag=2
		 and(  FaFangMonth between @Month_Start and  @Month_End)
		) 
		insert into @list_table_2(
		bmbh	
		,BasicWage
		,PostWage
		,WorkWage
		,JxWage
		,ManQin
		,Allowance
		,FixedAllowance
		,QtAllowance
		,TiCheng
		,MTiCheng
		,YjBonus
		,LsBonus
		,ChuQinKouKuan
		,BxKouKuan
		,ChiDaoKouKuan
		,YJKouKuan
		,OtherKouKuan
		,Count_Sum
		--
		,XSTC
		,XFTC
		,BLTC
		,LSwance
		,ZBwance
		,GRSDSKouKuan
		,GJJKouKuan
		,YFHeJi
		--
		) 
		select 
		bmbh	
		,BasicWage
		,PostWage
		,WorkWage
		,JxWage
		,ManQin
		,Allowance
		,FixedAllowance
		,QtAllowance
		,TiCheng
		,MTiCheng
		,YjBonus
		,LsBonus
		,ChuQinKouKuan
		,BxKouKuan
		,ChiDaoKouKuan
		,YJKouKuan
		,OtherKouKuan+ Deduction
		,Count_Sum
		--
		,XSTC
		,XFTC
		,BLTC
		,LSwance
		,ZBwance
		,GRSDSKouKuan
		,GJJKouKuan
		,YFHeJi
		--
		From list where bmbh is not null ;
		-- 上面完成 list 表
			--Select * from @list_table_2 
		;WITH list_Union As(
			Select 		
				bmbh
				,sum (BasicWage)  as BasicWage_s
				,sum (PostWage)  as PostWage_s
				,sum (WorkWage)  as WorkWage_s
				,sum (JxWage)  as JxWage_s
				,sum (ManQin)  as ManQin_s
				,sum (Allowance)  as Allowance_s
				,sum (FixedAllowance)  as FAllowance_s
				,sum (QtAllowance)  as QtAllowance_s
				,sum (TiCheng)  as TiCheng_s
				,sum (MTiCheng)  as MTiCheng_s
				,sum (YjBonus)  as YjBonus_s
				,sum (LsBonus)  as LsBonus_s
				,sum (ChuQinKouKuan)  as ChuQinKouKuan_s
				,sum (BxKouKuan)  as BxKouKuan_s
				,sum (ChiDaoKouKuan)  as ChiDaoKouKuan_s
				,sum (YJKouKuan)  as YJKouKuan_s
				,sum (OtherKouKuan)  as OtherKouKuan_s
				,sum (Count_Sum) as Count_Sum_s
				--
				,SUM(XSTC) AS XSTC_s
				,SUM(XFTC) AS XFTC_s
				,SUM(BLTC) AS BLTC_s
				,SUM(LSwance) AS LSwance_s
				,SUM(ZBwance) AS ZBwance_s
				,SUM(GRSDSKouKuan) AS GRSDSKouKuan_s
				,SUM(GJJKouKuan) AS GJJKouKuan_s
				,sum(YFHeJi) as YFHeJi_s
				--
		  From @list_table_2 WHERE bmbh is not null
		group by  bmbh
		--
		UNION All
		--
		Select 	
				--bmbh
				Convert(nchar(4), bmbh) as bmbh
				,0  as BasicWage_s
				,0  as PostWage_s
				,0  as WorkWage_s
				,0  as JxWage_s
				,0  as ManQin_s
				,0  as Allowance_s
				,0  as FAllowance_s
				,0  as QtAllowance_s
				,0  as TiCheng_s
				,0  as MTiCheng_s
				,0  as YjBonus_s
				,0  as LsBonus_s
				,0  as ChuQinKouKuan_s
				,0  as BxKouKuan_s
				,0  as ChiDaoKouKuan_s
				,0  as YJKouKuan_s
				,0  as OtherKouKuan_s
				,0  as Count_Sum_s
				--
				,0  AS XSTC_s
				,0  AS XFTC_s
				,0  AS BLTC_s
				,0  AS LSwance_s
				,0  AS ZBwance_s
				,0  AS GRSDSKouKuan_s
				,0  AS GJJKouKuan_s
				,0 as YFHeJi_s
				--
		From  department  
		where  (CompanyId=@CompanyId) and bmbh is not null  
		)
		select bmbh,(SELECT [DName]  FROM department WHERE bmbh=list_Union.bmbh)  AS [DName] 
		,0 as CompanyId --补充字段
		,'M'  As CompanyName --补充字段
		,sum (BasicWage_s)  as BasicWage_s
		,sum (PostWage_s)  as PostWage_s
		,sum (WorkWage_s)  as WorkWage_s
		,sum (JxWage_s)  as JxWage_s
		,sum (ManQin_s)  as ManQin_s
		,sum (Allowance_s)  as Allowance_s
		,sum (FAllowance_s)  as FAllowance_s
		,sum (QtAllowance_s)  as QtAllowance_s
		,sum (TiCheng_s)  as TiCheng_s
		,sum (MTiCheng_s)  as MTiCheng_s
		,sum (YjBonus_s)  as YjBonus_s
		,sum (LsBonus_s)  as LsBonus_s
		,sum (ChuQinKouKuan_s)  as ChuQinKouKuan_s
		,sum (BxKouKuan_s)  as BxKouKuan_s
		,sum (ChiDaoKouKuan_s)  as ChiDaoKouKuan_s
		,sum (YJKouKuan_s)  as YJKouKuan_s
		,sum (OtherKouKuan_s)  as OtherKouKuan_s
		,sum (Count_Sum_s) as Count_Sum_s
		--
		,SUM (XSTC_s) AS XSTC_s
		,SUM (XFTC_s) AS XFTC_s
		,SUM (BLTC_s) AS BLTC_s
		,SUM (LSwance_s) AS LSwance_s
		,SUM (ZBwance_s) AS ZBwance_s
		,SUM (GRSDSKouKuan_s) AS GRSDSKouKuan_s
		,SUM (GJJKouKuan_s) AS GJJKouKuan_s
		,sum(YFHeJi_s) as YFHeJi_s
		--
		from  list_Union  
		group by bmbh
		order by bmbh
		END
		--查询指定单位的全部部门汇总 结束
		

		if(@SerchType=3)
		Begin
		-- 执行 查询指定单位的指定部门汇总

		/*Departments 获取当前部门id的部门编号*/
			declare @bmbh_T  varchar(50)
			set @bmbh_T = (select bmbh  FROM department WHERE id=@DepId)

			declare @list_table_3 table(
			id int identity(1,1)
			,bmbh varchar(50)
			,BasicWage Decimal(18,2)
			,PostWage Decimal(18,2)
			,WorkWage Decimal(18,2)
			,JxWage Decimal(18,2)
			,ManQin Decimal(18,2)
			,Allowance Decimal(18,2)
			,FixedAllowance Decimal(18,2)
			,QtAllowance Decimal(18,2)
			,TiCheng Decimal(18,2)
			,MTiCheng Decimal(18,2)
			,YjBonus Decimal(18,2)
			,LsBonus Decimal(18,2)
			,ChuQinKouKuan Decimal(18,2)
			,BxKouKuan Decimal(18,2)
			,ChiDaoKouKuan Decimal(18,2)
			,YJKouKuan Decimal(18,2)
			,OtherKouKuan Decimal(18,2)
			,Count_Sum INT
			--
			,XSTC Decimal(18,2)
			,XFTC Decimal(18,2)
			,BLTC Decimal(18,2)
			,LSwance Decimal(18,2)
			,ZBwance Decimal(18,2)
			,GRSDSKouKuan Decimal(18,2)
			,GJJKouKuan Decimal(18,2)
			,YFHeJi Decimal(18,2)
			--
			)
			;WITH list As(
					Select 
					DepartmentId
					,(SELECT  bmbh  FROM department WHERE id=Employee_Wage.DepartmentId)  AS bmbh
					,DepName
					,GradeId
					,BasicWage
					,PostWage
					,WorkWage
					,JxWage
					,ManQin
					,Allowance
					,FixedAllowance
					,QtAllowance
					,TiCheng
					,MTiCheng
					,YjBonus
					,LsBonus
					,ChuQinKouKuan
					,BxKouKuan
					,ChiDaoKouKuan
					,YJKouKuan
					,OtherKouKuan,isnull(Deduction,0) as Deduction
					,FaFangMonth
					,1 as Count_Sum
					--
					,XSTC
					,XFTC
					,BLTC
					,LSwance
					,ZBwance
					,GRSDSKouKuan
					,GJJKouKuan
					,YFHeJi
					--
					From Employee_Wage  
					Where (CompanyId=@CompanyId) and (@GradeId=0 Or GradeId=@GradeId  ) and FinanceFlag=2
			 and(  FaFangMonth between @Month_Start and  @Month_End)
			) 
			insert into @list_table_3(
			bmbh	
			,BasicWage
			,PostWage
			,WorkWage
			,JxWage
			,ManQin
			,Allowance
			,FixedAllowance
			,QtAllowance
			,TiCheng
			,MTiCheng
			,YjBonus
			,LsBonus
			,ChuQinKouKuan
			,BxKouKuan
			,ChiDaoKouKuan
			,YJKouKuan
			,OtherKouKuan
			,Count_Sum
			--
			,XSTC
			,XFTC
			,BLTC
			,LSwance
			,ZBwance
			,GRSDSKouKuan
			,GJJKouKuan
			,YFHeJi
			--
			) 
			select 
			bmbh	
			,BasicWage
			,PostWage
			,WorkWage
			,JxWage
			,ManQin
			,Allowance
			,FixedAllowance
			,QtAllowance
			,TiCheng
			,MTiCheng
			,YjBonus
			,LsBonus
			,ChuQinKouKuan
			,BxKouKuan
			,ChiDaoKouKuan
			,YJKouKuan
			,OtherKouKuan+ Deduction
			,Count_Sum
			--
			,XSTC
			,XFTC
			,BLTC
			,LSwance
			,ZBwance
			,GRSDSKouKuan
			,GJJKouKuan
			,YFHeJi
			--
			From list 
			-- where bmbh like ''+@bmbh_T+'%' and bmbh is not null ;
			 where Left(bmbh,Len(@bmbh_T))=@bmbh_T;
			-- 上面完成 list 表
				--Select * from @list_table_3 
			;WITH list_Union As(
				Select 		
					bmbh
					,sum (BasicWage)  as BasicWage_s
					,sum (PostWage)  as PostWage_s
					,sum (WorkWage)  as WorkWage_s
					,sum (JxWage)  as JxWage_s
					,sum (ManQin)  as ManQin_s
					,sum (Allowance)  as Allowance_s
					,sum (FixedAllowance)  as FAllowance_s
					,sum (QtAllowance)  as QtAllowance_s
					,sum (TiCheng)  as TiCheng_s
					,sum (MTiCheng)  as MTiCheng_s
					,sum (YjBonus)  as YjBonus_s
					,sum (LsBonus)  as LsBonus_s
					,sum (ChuQinKouKuan)  as ChuQinKouKuan_s
					,sum (BxKouKuan)  as BxKouKuan_s
					,sum (ChiDaoKouKuan)  as ChiDaoKouKuan_s
					,sum (YJKouKuan)  as YJKouKuan_s
					,sum (OtherKouKuan)  as OtherKouKuan_s
					,sum (Count_Sum) as Count_Sum_s
					--
					,SUM(XSTC) AS XSTC_s
					,SUM(XFTC) AS XFTC_s
					,SUM(BLTC) AS BLTC_s
					,SUM(LSwance) AS LSwance_s
					,SUM(ZBwance) AS ZBwance_s
					,SUM(GRSDSKouKuan) AS GRSDSKouKuan_s
					,SUM(GJJKouKuan) AS GJJKouKuan_s
					,sum (YFHeJi) as YFHeJi_s
					--
			  From @list_table_3 WHERE bmbh is not null
			group by  bmbh
			--
			UNION All
			--
			Select 	
					bmbh
					--Convert(nchar(4), bmbh) as bmbh
					,0  as BasicWage_s
					,0  as PostWage_s
					,0  as WorkWage_s
					,0  as JxWage_s
					,0  as ManQin_s
					,0  as Allowance_s
					,0  as FAllowance_s
					,0  as QtAllowance_s
					,0  as TiCheng_s
					,0  as MTiCheng_s
					,0  as YjBonus_s
					,0  as LsBonus_s
					,0  as ChuQinKouKuan_s
					,0  as BxKouKuan_s
					,0  as ChiDaoKouKuan_s
					,0  as YJKouKuan_s
					,0  as OtherKouKuan_s
					,0 as Count_Sum_s
					--
					,0  AS XSTC_s
					,0  AS XFTC_s
					,0  AS BLTC_s
					,0  AS LSwance_s
					,0  AS ZBwance_s
					,0  AS GRSDSKouKuan_s
					,0  AS GJJKouKuan_s
					,0 as YFHeJi_s
					--
			From  department  
			where  (CompanyId=@CompanyId) 
			-- and  bmbh like ''+@bmbh_T+'%' and  bmbh is not null  
			 and  Left(bmbh,Len(@bmbh_T))=@bmbh_T
			)
			select bmbh,(SELECT [DName]  FROM department WHERE bmbh=list_Union.bmbh)  AS [DName] 
			,0 as CompanyId --补充字段
			,'N'  As CompanyName --补充字段
			,sum (BasicWage_s)  as BasicWage_s
			,sum (PostWage_s)  as PostWage_s
			,sum (WorkWage_s)  as WorkWage_s
			,sum (JxWage_s)  as JxWage_s
			,sum (ManQin_s)  as ManQin_s
			,sum (Allowance_s)  as Allowance_s
			,sum (FAllowance_s)  as FAllowance_s
			,sum (QtAllowance_s)  as QtAllowance_s
			,sum (TiCheng_s)  as TiCheng_s
			,sum (MTiCheng_s)  as MTiCheng_s
			,sum (YjBonus_s)  as YjBonus_s
			,sum (LsBonus_s)  as LsBonus_s
			,sum (ChuQinKouKuan_s)  as ChuQinKouKuan_s
			,sum (BxKouKuan_s)  as BxKouKuan_s
			,sum (ChiDaoKouKuan_s)  as ChiDaoKouKuan_s
			,sum (YJKouKuan_s)  as YJKouKuan_s
			,sum (OtherKouKuan_s)  as OtherKouKuan_s
			,sum (Count_Sum_s) as Count_Sum_s
			--
			,SUM (XSTC_s) AS XSTC_s
			,SUM (XFTC_s) AS XFTC_s
			,SUM (BLTC_s) AS BLTC_s
			,SUM (LSwance_s) AS LSwance_s
			,SUM (ZBwance_s) AS ZBwance_s
			,SUM (GRSDSKouKuan_s) AS GRSDSKouKuan_s
			,SUM (GJJKouKuan_s) AS GJJKouKuan_s
			,sum(YFHeJi_s) as YFHeJi_s
			--
			from  list_Union  
			group by bmbh
			order by bmbh
		END
		--执行 查询指定单位的指定部门汇总

End
	RETURN
